﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DAL;
using System.Data.Linq;
using BOL;
using System.Data.SqlClient;
using System.IO;
using System.Data;
using DevComponents.DotNetBar;
using System.Windows.Forms;

namespace BLL
{
    public class frmM
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();
        
    }

    public class DangNhap
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();

        public int checklog(string taikhoan, string matkhau)
        {
            int kq = 0;
            if (db.TAIKHOANs.Where(tk => tk.TAIKHOAN1 == taikhoan).FirstOrDefault() != null)
            {
                if ((db.TAIKHOANs.Where(tk => (tk.TAIKHOAN1).Trim() == taikhoan).FirstOrDefault().MATKHAU).Trim() == matkhau)
                    kq = 1;
                else kq = 2;
            }
            return kq;
        }
    }

    public class ChucNangNhanVien
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext(); 
        public string laychucvu(string taikhoan)
        {
            return db.TAIKHOANs.Where(tk => tk.TAIKHOAN1 == taikhoan).FirstOrDefault().CHUCVU.Trim();
        }

              
        public List<thongtinnhanvien> laynhanvien(string taikhoan)
        {
            var bien = (from nv in db.NHANVIENs
                       from tk in db.TAIKHOANs
                       where nv.MANHANVIEN == tk.MANHANVIEN && tk.TAIKHOAN1 == taikhoan
                        select new thongtinnhanvien
                       {
                           ma = nv.MANHANVIEN,
                           ten = nv.TENNHANVIEN,
                           gioitinh = nv.GIOITINHNV,
                           //vãi cả ngày, chưa tìm đc cái tối ưu
                           ngaysinh = DateTime.Parse(nv.NGAYSINHNV.ToString()).ToString("dd/MM/yyyy"),
                           noisinh = nv.NOISINHNV,
                           diachi = nv.DIACHINV,
                           sodt = nv.SDTNV,
                           cmnd = nv.CMNDNV,                           
                           hinhanh = new MemoryStream(nv.HINHANHNV.ToArray()),
                           matkhau = tk.MATKHAU.Trim(),
                           chucvu = tk.CHUCVU                           
                       }).ToList<thongtinnhanvien>();

            return bien;
        }

    }

    public class ThongtinNhanVien
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();

        public void SuathongTin(string ma, string ten, string gioitinh, DateTime ngaysinh, string noisinh, string diachi, string sdt, string cmnd, MemoryStream hinhanh)
        {          
            
            NHANVIEN nv = db.NHANVIENs.Where(p => p.MANHANVIEN == ma).FirstOrDefault();
            nv.TENNHANVIEN = ten;
            nv.GIOITINHNV = gioitinh;
            nv.NGAYSINHNV = ngaysinh;
            nv.NOISINHNV = noisinh;
            nv.DIACHINV = diachi;
            nv.SDTNV = sdt;
            nv.CMNDNV = cmnd;
            nv.HINHANHNV =hinhanh.ToArray();
            db.SubmitChanges();

        }
        
    }

    public class DoiMatKhau
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();

        public string getmk(string taikhoan)
        {
            return db.TAIKHOANs.Where(tk => tk.TAIKHOAN1.Trim() == taikhoan).FirstOrDefault().MATKHAU.ToString().Trim();
        }

        public void suamatkhau(string taikhoandn, string matkhau)
        {
            TAIKHOAN tk = db.TAIKHOANs.Where(t => t.TAIKHOAN1.Trim() == taikhoandn).FirstOrDefault();
            tk.MATKHAU = matkhau;
            db.SubmitChanges();
        }
    }

    public class CapNhatNoiDen
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();

        public List<thongtinnoiden> laynoiden()
        {
            var bien = (from nd in db.NOIDENs                  
                        select new thongtinnoiden
                       {
                           ma = nd.MANOIDEN,
                           ten = nd.TENNOIDEN                         
                       }).ToList<thongtinnoiden>();

            return bien;
        }

        public string manoidenlonnhat()
        {
            string ma = (from nd in db.NOIDENs
                        orderby nd.MANOIDEN descending
                        select nd.MANOIDEN).FirstOrDefault();
            return ma;
        }

        public void themnoiden(string ma, string ten)
        {
            NOIDEN nd = new NOIDEN();
            nd.MANOIDEN = ma;
            nd.TENNOIDEN = ten;

            db.NOIDENs.InsertOnSubmit(nd);
            db.SubmitChanges();

        }

        public void suanoiden(string ma, string ten)
        {
            NOIDEN nd = db.NOIDENs.Where(d => d.MANOIDEN == ma).FirstOrDefault();
            nd.TENNOIDEN = ten;

            db.SubmitChanges();
        }
       
        public void xoanoiden(string ma)
        {
            NOIDEN nd = db.NOIDENs.Where(d => d.MANOIDEN == ma).FirstOrDefault();

            db.NOIDENs.DeleteOnSubmit(nd);
            db.SubmitChanges();
        }

    }

    public class CapNhatNoiDi
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();

        public List<thongtinnoidi> laynoidi()
        {
            var bien = (from nd in db.NOIDIs
                        select new thongtinnoidi
                        {
                            ma = nd.MANOIDI,
                            ten = nd.TENNOIDI
                        }).ToList<thongtinnoidi>();

            return bien;
        }

        public string manoidilonnhat()
        {
            string ma = (from nd in db.NOIDIs
                         orderby nd.MANOIDI descending
                         select nd.MANOIDI).FirstOrDefault();
            return ma;
        }

        public void themnoidi(string ma, string ten)
        {
            NOIDI nd = new NOIDI();
            nd.MANOIDI= ma;
            nd.TENNOIDI = ten;

            db.NOIDIs.InsertOnSubmit(nd);
            db.SubmitChanges();

        }

        public void suanoidi(string ma, string ten)
        {
            NOIDI nd = db.NOIDIs.Where(d => d.MANOIDI == ma).FirstOrDefault();
            nd.TENNOIDI = ten;

            db.SubmitChanges();
        }

        public void xoanoidi(string ma)
        {
            NOIDI nd = db.NOIDIs.Where(d => d.MANOIDI == ma).FirstOrDefault();

            db.NOIDIs.DeleteOnSubmit(nd);
            db.SubmitChanges();
        }
    }

    public class CapNhatDiemDung
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();

        public List<thongtindiemdung> laydiemdung()
        {
            var bien = (from dd in db.DIEMDUNGCHANs
                        select new thongtindiemdung
                        {
                            ma = dd.MADIEMDC,
                            ten = dd.TENDIEMDC
                        }).ToList<thongtindiemdung>();

            return bien;
        }

        public string madiemdunglonnhat()
        {
            string ma = (from dd in db.DIEMDUNGCHANs
                         orderby dd.MADIEMDC descending
                         select dd.MADIEMDC).FirstOrDefault();
            return ma;
        }

        public void themdiemdung(string ma, string ten)
        {
            DIEMDUNGCHAN dd = new DIEMDUNGCHAN();
            dd.MADIEMDC = ma;
            dd.TENDIEMDC = ten;

            db.DIEMDUNGCHANs.InsertOnSubmit(dd);
            db.SubmitChanges();

        }

        public void suadiemdung(string ma, string ten)
        {
            DIEMDUNGCHAN dd = db.DIEMDUNGCHANs.Where(d => d.MADIEMDC == ma).FirstOrDefault();
            dd.TENDIEMDC = ten;

            db.SubmitChanges();
        }

        public void xoadiemdung(string ma)
        {
            DIEMDUNGCHAN dd = db.DIEMDUNGCHANs.Where(d => d.MADIEMDC == ma).FirstOrDefault();

            db.DIEMDUNGCHANs.DeleteOnSubmit(dd);
            db.SubmitChanges();
        }
    }

    public class CapNhatTuyenDuong
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();
        
        public List<thongtintuyenduong> laytuyenduong()
        {
            var bien = (from td in db.TUYENDUONGs
                        from nd in db.NOIDIs
                        from nden in db.NOIDENs
                        from diemdung in db.DIEMDUNGCHANs
                        where td.MANOIDI == nd.MANOIDI && td.MANOIDEN == nden.MANOIDEN && td.MADIEMDC == diemdung.MADIEMDC
                        select new thongtintuyenduong
                        {
                            ma = td.MATUYEN,
                            diemdi = nd.TENNOIDI,
                            diemdung = diemdung.TENDIEMDC,
                            diemden = nden.TENNOIDEN
                        }).ToList<thongtintuyenduong>();

            return bien;
        }



        public string matuyenlonnhat()
        {
            string ma = (from tt in db.TUYENDUONGs
                         orderby tt.MATUYEN descending
                         select tt.MATUYEN).FirstOrDefault();
            return ma;
        }

        public void themtuyenduong(string ma, string noidi, string diemdung, string noiden)
        {
            TUYENDUONG tt = new TUYENDUONG();
            tt.MATUYEN = ma;
            tt.MADIEMDC = diemdung;
            tt.MANOIDEN = noiden;
            tt.MANOIDI = noidi;

            db.TUYENDUONGs.InsertOnSubmit(tt);
            db.SubmitChanges();

        }

        public void suatuyenduong(string ma, string noidi, string diemdung, string noiden)
        {            
            TUYENDUONG tt = db.TUYENDUONGs.Where(t => t.MATUYEN == ma).FirstOrDefault();
            tt.MANOIDI = noidi;
            tt.MANOIDEN = noiden;
            tt.MADIEMDC = diemdung;

            db.SubmitChanges();
        }

        public void xoatuyenduong(string ma)
        {
            TUYENDUONG tt = db.TUYENDUONGs.Where(t => t.MATUYEN == ma).FirstOrDefault();

            db.TUYENDUONGs.DeleteOnSubmit(tt);
            db.SubmitChanges();
        }
    }

    public class CapNhatGiaTuyen
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();

        public List<thongtintdgia> laytuyen()
        {
            var bien = (from td in db.TUYENDUONGs
                        from nd in db.NOIDIs
                        from nden in db.NOIDENs
                        where td.MANOIDEN == nden.MANOIDEN && td.MANOIDI == nd.MANOIDI
                        select new thongtintdgia
                        {
                            ma = td.MATUYEN,
                            ten = nd.TENNOIDI +" ==> "+ nden.TENNOIDEN
                        }).ToList<thongtintdgia>();

            return bien;
        }

        public List<giave> laygia()
        {
            var bien = (from gia in db.DONGIAs                        
                        select new giave
                        {
                            gia = gia.GIATIEN                           
                        }).ToList<giave>();           
            return bien;
        }
       
        public List<giatuyencapnhatgannhat> laygiagannhat()
        {
            var bien = (from g in db.DONGIAs
                        from gt in db.GIATUYENs
                        from t in db.TUYENDUONGs  
                        from noidi in db.NOIDIs
                        from noiden in db.NOIDENs
                        from abc in (from abc in db.GIATUYENs
                        group abc by abc.MATUYEN into capnhatgannhat
                        select new
                        {
                            matuyen = capnhatgannhat.Key,
                            ngaycapnhatgannhat = capnhatgannhat.Max(a=>a.NGAYCAPNHAT)
                        })
                        where g.MADONGIA == gt.MADONGIA && t.MATUYEN == gt.MATUYEN && t.MANOIDI == noidi.MANOIDI && t.MANOIDEN == noiden.MANOIDEN  && gt.MATUYEN == abc.matuyen && gt.NGAYCAPNHAT == abc.ngaycapnhatgannhat
                        select new giatuyencapnhatgannhat
                        {
                            tuyen = noidi.TENNOIDI +" ==> "+ noiden.TENNOIDEN,
                            dongia = g.GIATIEN,
                            ngaycapnhatgannhat = gt.NGAYCAPNHAT,
                        }).ToList<giatuyencapnhatgannhat>();
            return bien;
        }

        public List<giatuyencapnhatgannhat> laygiatuyen(string matuyen)
        {
            var bien = (from g in db.DONGIAs
                        from gt in db.GIATUYENs
                        from t in db.TUYENDUONGs
                        from noidi in db.NOIDIs
                        from noiden in db.NOIDENs                        
                        where g.MADONGIA == gt.MADONGIA && t.MATUYEN == gt.MATUYEN && t.MANOIDI == noidi.MANOIDI && t.MANOIDEN == noiden.MANOIDEN  && gt.MATUYEN == matuyen
                        select new giatuyencapnhatgannhat
                        {
                            tuyen = noidi.TENNOIDI + " ==> " + noiden.TENNOIDEN,
                            dongia = g.GIATIEN,
                            ngaycapnhatgannhat = gt.NGAYCAPNHAT,
                        }).ToList<giatuyencapnhatgannhat>();
            return bien;
        }

        public string NextID(string lastID, string prefixID)
        {
            int nextID = int.Parse(lastID.Remove(0, prefixID.Length)) + 1;
            int lengthNumerID = lastID.Length - prefixID.Length;
            string zeroNumber = "";
            for (int i = 1; i <= lengthNumerID; i++)
            {
                if (nextID < Math.Pow(10, i))
                {
                    for (int j = 1; j <= lengthNumerID - i; i++)
                    {
                        zeroNumber += "0";
                    }
                    return prefixID + zeroNumber + nextID.ToString();
                }
            }
            return prefixID + nextID;

        }

        public void themgiatuyen(string matuyen, DateTime ngay, string gia)
        {
            var magia = db.DONGIAs.Where(g => g.GIATIEN == int.Parse(gia)).FirstOrDefault();
            if (magia == null)
            {
                DialogResult dr = MessageBoxEx.Show("Giá " + gia + " chưa có trong hệ thống, bạn có muốn lưu và cập nhật không ?", "Thông báo", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (dr == DialogResult.Yes)
                {
                    //lấy mã giá lớn nhất
                    string ma = (from mg in db.DONGIAs
                                 orderby mg.MADONGIA descending
                                 select mg.MADONGIA).FirstOrDefault();
                    //tạo mã kế tiếp
                    string magianext = NextID(ma, "DG");
                    //insert vào bảng dongia
                    DONGIA dg = new DONGIA();
                    dg.MADONGIA = magianext;
                    dg.GIATIEN = int.Parse(gia);

                    db.DONGIAs.InsertOnSubmit(dg);
                    db.SubmitChanges();

                    //insert vào bảng giatuyen

                    GIATUYEN gt = new GIATUYEN();
                    gt.MATUYEN = matuyen;
                    gt.NGAYCAPNHAT = ngay;
                    gt.MADONGIA = magianext;

                    db.GIATUYENs.InsertOnSubmit(gt);
                    db.SubmitChanges();
                }
            }
            else
            {
                GIATUYEN gt = new GIATUYEN();
                gt.MATUYEN = matuyen;
                gt.NGAYCAPNHAT = ngay;
                gt.MADONGIA = magia.MADONGIA;

                db.GIATUYENs.InsertOnSubmit(gt);
                db.SubmitChanges();
            }
            

        }

        public int tuyenchuacogia()
        {
            var bien = (from td in db.TUYENDUONGs
                        where !(from gt in db.GIATUYENs
                                select gt.MATUYEN).Contains(td.MATUYEN)
                        select td.MATUYEN);
            return bien.Count(); ;
        }
    }

    public class CapNhatNhanVienPhuTrachXe
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();

        public List<nhanvienphutrachxe> laydsnhanvienpt()
        {
            var bien = (from nv in db.NHANVIENPHUTRACHXEs
                        from xe in db.XEs
                        where nv.MAXE == xe.MAXE
                        select new nhanvienphutrachxe
                        {
                           manv = nv.MANVPT,
                           hoten = nv.TENNVPT,
                           gioitinh = nv.GIOITINHNVPT,
                           ngaysinh = DateTime.Parse(nv.NGAYSINHNVPT.ToString()).ToString("dd/MM/yyyy"),
                           diachi = nv.DIACHINVPT,
                           soCMND = nv.CMNDNVPT,
                           sodt = nv.SDTNVPT,
                           chucvu = nv.CHUCVUNVPT,
                           xe = xe.BIENSO
                        }).ToList<nhanvienphutrachxe>();

            return bien;
        }

        public List<nhanvienphutrachxe> laydsnhanvienpt(string tukhoa)
        {
            var bien = (from nv in db.NHANVIENPHUTRACHXEs
                        from xe in db.XEs
                        where nv.MAXE == xe.MAXE && (nv.MANVPT.Contains(tukhoa) || nv.TENNVPT.Contains(tukhoa))
                        select new nhanvienphutrachxe
                        {
                            manv = nv.MANVPT,
                            hoten = nv.TENNVPT,
                            gioitinh = nv.GIOITINHNVPT,
                            ngaysinh = DateTime.Parse(nv.NGAYSINHNVPT.ToString()).ToString("dd/MM/yyyy"),
                            diachi = nv.DIACHINVPT,
                            soCMND = nv.CMNDNVPT,
                            sodt = nv.SDTNVPT,
                            chucvu = nv.CHUCVUNVPT,
                            xe = xe.BIENSO
                        }).ToList<nhanvienphutrachxe>();

            return bien;
        }

        public List<biensoxe> layxe()
        {
            var bien = (from xe in db.XEs
                        select new biensoxe
                        {
                            maxe = xe.MAXE,
                            bienxe = xe.BIENSO
                        }).ToList<biensoxe>();

            return bien;
        }

        public string manvptlonnhat()
        {
            string ma = (from nv in db.NHANVIENPHUTRACHXEs
                         orderby nv.MANVPT descending
                         select nv.MANVPT).FirstOrDefault();
            return ma;
        }


        public void themnvpt(string ma, string ten, string gioitinh, DateTime ngaysinh, string diachi, string sdt, string cmnd, MemoryStream hinhanh, string chucvu, string maxe)
        {
            NHANVIENPHUTRACHXE nvptx = new NHANVIENPHUTRACHXE();           
            nvptx.MANVPT = ma;
            nvptx.TENNVPT = ten;
            nvptx.GIOITINHNVPT = gioitinh;
            nvptx.NGAYSINHNVPT = ngaysinh;
            nvptx.DIACHINVPT = diachi;
            nvptx.SDTNVPT = sdt;
            nvptx.CMNDNVPT = cmnd;
            nvptx.HINHANHNVPT = hinhanh.ToArray();
            nvptx.CHUCVUNVPT = chucvu;
            nvptx.MAXE = maxe;

            db.NHANVIENPHUTRACHXEs.InsertOnSubmit(nvptx);
            db.SubmitChanges();
        }

        public void suanvpt(string ma, string ten, string gioitinh, DateTime ngaysinh, string diachi, string sdt, string cmnd, MemoryStream hinhanh, string chucvu, string maxe)
        {
            NHANVIENPHUTRACHXE nvptx = db.NHANVIENPHUTRACHXEs.Where(nv => nv.MANVPT == ma).FirstOrDefault();           
            nvptx.TENNVPT = ten;
            nvptx.GIOITINHNVPT = gioitinh;
            nvptx.NGAYSINHNVPT = ngaysinh;
            nvptx.DIACHINVPT = diachi;
            nvptx.SDTNVPT = sdt;
            nvptx.CMNDNVPT = cmnd;
            nvptx.HINHANHNVPT = hinhanh.ToArray();
            nvptx.CHUCVUNVPT = chucvu;
            nvptx.MAXE = maxe;
           
            db.SubmitChanges();
        }

        public void xoanvpt(string ma)
        {
            NHANVIENPHUTRACHXE nvpt = db.NHANVIENPHUTRACHXEs.Where(nv => nv.MANVPT == ma).FirstOrDefault();

            db.NHANVIENPHUTRACHXEs.DeleteOnSubmit(nvpt);
            db.SubmitChanges();
        }

        public MemoryStream layhinhnvpt(string ma)
        {
            return new MemoryStream(db.NHANVIENPHUTRACHXEs.Where(nv => nv.MANVPT == ma).FirstOrDefault().HINHANHNVPT.ToArray());
        }
       
    }

    public class CapNhatXe
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();

        public List<xe> laydsxe()
        {
            var kq = (from x in db.XEs
                     select new xe
                        {
                            maxe = x.MAXE,
                            tenxe = x.TENXE,
                            bienxe = x.BIENSO,
                            sochongoi = int.Parse(x.SOCHONGOI.ToString())
                        }).ToList<xe>();
            
            return kq;
        }

        public List<xe> laydsxe(string tukhoa)
        {
            var kq = (from x in db.XEs
                      where x.TENXE.Contains(tukhoa) || x.BIENSO.Contains(tukhoa)
                      select new xe
                      {
                          maxe = x.MAXE,
                          tenxe = x.TENXE,
                          bienxe = x.BIENSO,
                          sochongoi = int.Parse(x.SOCHONGOI.ToString())
                      }).ToList<xe>();

            return kq;
        }

        public void themxe(string ma, string ten, string bienso, int sochongoi)
        { 
            XE xe = new XE();
            xe.MAXE = ma;
            xe.TENXE = ten;
            xe.BIENSO = bienso;
            xe.SOCHONGOI = sochongoi;

            db.XEs.InsertOnSubmit(xe);
            db.SubmitChanges();
        }

        public void suaxe(string ma, string ten, string bienso, int sochongoi)
        {
            XE xe = db.XEs.Where(x => x.MAXE == ma).FirstOrDefault();            
            xe.TENXE = ten;
            xe.BIENSO = bienso;
            xe.SOCHONGOI = sochongoi;
            
            db.SubmitChanges();
        }

        public void xoaxe(string ma)
        {
            XE xe = db.XEs.Where(x => x.MAXE == ma).FirstOrDefault();

            db.XEs.DeleteOnSubmit(xe);
            db.SubmitChanges();
        }

        public string maxelonnhat()
        {
            string ma = (from x in db.XEs
                         orderby x.MAXE descending
                         select x.MAXE).FirstOrDefault();
            return ma;
        }

    }

    public class CapNhatLichChay
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();

        public List<lichchay> laydslichchay()
        {
            var kq = (from lc in db.LICHCHAYs
                      from td in db.TUYENDUONGs
                      from x in  db.XEs
                      from noiden in db.NOIDENs
                      from noidi in db.NOIDIs
                      where lc.MAXE == x.MAXE && lc.MATUYEN == td.MATUYEN && noiden.MANOIDEN == td.MANOIDEN && noidi.MANOIDI == td.MANOIDI && lc.NGAYCHAY > System.DateTime.Now
                      select new lichchay
                      {
                         tuyendg = noidi.TENNOIDI +" ==> "+ noiden.TENNOIDEN,
                         xe = x.BIENSO,
                         ngaykh = DateTime.Parse(lc.NGAYCHAY.ToString()).ToString("dd/MM/yyyy"),
                         giokh = lc.GIO.ToString(),
                         thoigianchay = lc.THOIGIANCHAY.ToString(),
                         huychuyen = lc.HUY.ToString() == "0" ? "":"X"
                      }).ToList<lichchay>();

            return kq;
        }

        public List<lichchay> laydslichchay(string tukhoa)
        {
            var kq = (from lc in db.LICHCHAYs
                      from td in db.TUYENDUONGs
                      from x in db.XEs
                      from noiden in db.NOIDENs
                      from noidi in db.NOIDIs
                      where lc.MAXE == x.MAXE && lc.MATUYEN == td.MATUYEN && noiden.MANOIDEN == td.MANOIDEN && noidi.MANOIDI == td.MANOIDI && lc.NGAYCHAY > System.DateTime.Now &&(noiden.TENNOIDEN.Contains(tukhoa) || noidi.TENNOIDI.Contains(tukhoa)||x.BIENSO.Contains(tukhoa))
                      select new lichchay
                      {
                          tuyendg = noidi.TENNOIDI + " ==> " + noiden.TENNOIDEN,
                          xe = x.BIENSO,
                          ngaykh = DateTime.Parse(lc.NGAYCHAY.ToString()).ToString("dd/MM/yyyy"),
                          giokh = lc.GIO.ToString(),
                          thoigianchay = lc.THOIGIANCHAY.ToString(),
                          huychuyen = lc.HUY.ToString() == "0" ? "" : "X"
                      }).ToList<lichchay>();

            return kq;
        }

        public void themlichchay(string matuyen, string maxe, DateTime ngaychay, int giochay, int tgchay, int huy)
        {
            LICHCHAY lc = new LICHCHAY();
            lc.MATUYEN = matuyen;
            lc.MAXE = maxe;
            lc.NGAYCHAY = ngaychay;
            lc.GIO = giochay;
            lc.THOIGIANCHAY = tgchay;
            lc.HUY = huy;

            db.LICHCHAYs.InsertOnSubmit(lc);
            db.SubmitChanges();
        }

        public void sualichchay(string matuyen, string maxe, DateTime ngaychay, int giochay, int tgchay, int huy)
        {
            LICHCHAY lc = db.LICHCHAYs.Where(a => a.MATUYEN == matuyen && a.MAXE == maxe && a.NGAYCHAY == ngaychay && a.GIO == giochay).FirstOrDefault();           
            lc.THOIGIANCHAY = tgchay;
            lc.HUY = huy;
            
            db.SubmitChanges();
        }

        public void xoalichchay(string matuyen, string maxe, DateTime ngaychay, int giochay)
        {
            LICHCHAY lc = db.LICHCHAYs.Where(a => a.MATUYEN == matuyen && a.MAXE == maxe && a.NGAYCHAY == ngaychay && a.GIO == giochay).FirstOrDefault();

            db.LICHCHAYs.DeleteOnSubmit(lc);
            db.SubmitChanges();
        }

    }

    public class CapnhatKhachHang
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();

        public List<khachhang> laydskhachhang()
        {
            var kq = (from kh in db.KHACHHANGs
                      select new khachhang
                      {
                          makh = kh.MAKHACHHANG,
                          tenkh = kh.TENKHACHHANG,
                          ngaysinhkh = DateTime.Parse(kh.NGAYSINHKH.ToString()).ToString("dd/MM/yyyy"),
                          gioitinhkh = kh.GIOITINHKH,
                          diachikh = kh.DIACHIKH,
                          sdtkh = kh.SDTKH
                      }).ToList<khachhang>();

            return kq;
        }

        public List<khachhang> laydskhachhang(string tukhoa)
        {
            var kq = (from kh in db.KHACHHANGs
                      where kh.SDTKH.Contains(tukhoa) || kh.MAKHACHHANG.Contains(tukhoa) || kh.TENKHACHHANG.Contains(tukhoa)
                      select new khachhang
                      {
                          makh = kh.MAKHACHHANG,
                          tenkh = kh.TENKHACHHANG,
                          ngaysinhkh = DateTime.Parse(kh.NGAYSINHKH.ToString()).ToString("dd/MM/yyyy"),
                          gioitinhkh = kh.GIOITINHKH,
                          diachikh = kh.DIACHIKH,
                          sdtkh = kh.SDTKH
                      }).ToList<khachhang>();

            return kq;
        }

        public void themkh(string ma, string ten, string gioitinh, DateTime ngaysinh, string diachi, string sdt)
        {
            KHACHHANG kh = new KHACHHANG();
            kh.MAKHACHHANG = ma;
            kh.TENKHACHHANG = ten;
            kh.GIOITINHKH = gioitinh;
            kh.DIACHIKH = diachi;
            kh.SDTKH = sdt;
            kh.NGAYSINHKH = ngaysinh;
            db.KHACHHANGs.InsertOnSubmit(kh);
            db.SubmitChanges();
        }

        public void suakh(string ma, string ten, string gioitinh, DateTime ngaysinh, string diachi, string sdt)
        {
            KHACHHANG kh = db.KHACHHANGs.Where(k => k.MAKHACHHANG == ma).FirstOrDefault();
            
            kh.TENKHACHHANG = ten;
            kh.GIOITINHKH = gioitinh;
            kh.DIACHIKH = diachi;
            kh.SDTKH = sdt;
            
            db.SubmitChanges();
        }

        public void xoakh(string ma)
        {
            KHACHHANG kh = db.KHACHHANGs.Where(k => k.MAKHACHHANG == ma).FirstOrDefault();
            
           db.KHACHHANGs.DeleteOnSubmit(kh);
            
            db.SubmitChanges();
        }

        public string makhlonnhat()
        {
            string ma = (from x in db.KHACHHANGs
                         orderby x.MAKHACHHANG descending
                         select x.MAKHACHHANG).FirstOrDefault();
            return ma;
        }
    }

    public class CapNhatNhanVien
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();
        public List<nhanvien> laynhanvien()
        {
            var bien = (from nv in db.NHANVIENs
                        select new nhanvien
                        {
                            ma = nv.MANHANVIEN,
                            ten = nv.TENNHANVIEN,
                            gioitinh = nv.GIOITINHNV,
                            //vãi cả ngày, chưa tìm đc cái tối ưu
                            ngaysinh = DateTime.Parse(nv.NGAYSINHNV.ToString()).ToString("dd/MM/yyyy"),
                            noisinh = nv.NOISINHNV,
                            diachi = nv.DIACHINV,
                            sodt = nv.SDTNV,
                            cmnd = nv.CMNDNV,                           
                        }).ToList<nhanvien>();

            return bien;
        }

        public List<nhanvien> laynhanvien(string tukhoa)
        {
            var bien = (from nv in db.NHANVIENs
                        where nv.MANHANVIEN.Contains(tukhoa) || nv.TENNHANVIEN.Contains(tukhoa) || nv.SDTNV.Contains(tukhoa)
                        select new nhanvien
                        {
                            ma = nv.MANHANVIEN,
                            ten = nv.TENNHANVIEN,
                            gioitinh = nv.GIOITINHNV,
                            //vãi cả ngày, chưa tìm đc cái tối ưu
                            ngaysinh = DateTime.Parse(nv.NGAYSINHNV.ToString()).ToString("dd/MM/yyyy"),
                            noisinh = nv.NOISINHNV,
                            diachi = nv.DIACHINV,
                            sodt = nv.SDTNV,
                            cmnd = nv.CMNDNV,
                        }).ToList<nhanvien>();

            return bien;
        }

        public MemoryStream layhinhnv(string ma)
        {
            return new MemoryStream(db.NHANVIENs.Where(nv => nv.MANHANVIEN == ma).FirstOrDefault().HINHANHNV.ToArray());
        }

        public void themnv(string ma, string ten, string gioitinh, DateTime ngaysinh,string noisinh, string diachi, string sdt, string cmnd, MemoryStream hinhanh)
        {
            NHANVIEN nv = new NHANVIEN();
            nv.MANHANVIEN = ma;
            nv.TENNHANVIEN = ten;
            nv.GIOITINHNV = gioitinh; 
            nv.NGAYSINHNV = ngaysinh;
            nv.NOISINHNV = noisinh;
            nv.DIACHINV = diachi;
            nv.SDTNV = sdt;
            nv.CMNDNV = cmnd;
            nv.HINHANHNV = hinhanh.ToArray();

            db.NHANVIENs.InsertOnSubmit(nv);
            db.SubmitChanges();
        }

        public void suanv(string ma, string ten, string gioitinh, DateTime ngaysinh, string noisinh, string diachi, string sdt, string cmnd, MemoryStream hinhanh)
        {
            NHANVIEN nv = db.NHANVIENs.Where(v => v.MANHANVIEN == ma).FirstOrDefault();            
            nv.TENNHANVIEN = ten;
            nv.GIOITINHNV = gioitinh;
            nv.NGAYSINHNV = ngaysinh;
            nv.NOISINHNV = noisinh;
            nv.DIACHINV = diachi;
            nv.SDTNV = sdt;
            nv.CMNDNV = cmnd;
            nv.HINHANHNV = hinhanh.ToArray();
           
            db.SubmitChanges();
        }

        public void xoanv(string ma)
        {
            NHANVIEN nv = db.NHANVIENs.Where(v => v.MANHANVIEN == ma).FirstOrDefault();

            db.NHANVIENs.DeleteOnSubmit(nv);
            db.SubmitChanges();
        }

        public string manvlonnhat()
        {
            string ma = (from nv in db.NHANVIENs
                         orderby nv.MANHANVIEN descending
                         select nv.MANHANVIEN).FirstOrDefault();
            return ma;
        }

        public int nvchuacotk()
        {
            var bien = (from nv in db.NHANVIENs
                        where !(from tk in db.TAIKHOANs
                                select tk.MANHANVIEN).Contains(nv.MANHANVIEN)
                        select nv.MANHANVIEN);
            return bien.Count(); ;
        }
    }

    public class BanVe
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();
        public List<khachhang> laydskhachhang(string ma)
        {
            var kq = (from kh in db.KHACHHANGs
                      where kh.MAKHACHHANG == ma
                      select new khachhang
                      {
                          makh = kh.MAKHACHHANG,
                          tenkh = kh.TENKHACHHANG,
                          ngaysinhkh = DateTime.Parse(kh.NGAYSINHKH.ToString()).ToString("dd/MM/yyyy"),
                          gioitinhkh = kh.GIOITINHKH,
                          diachikh = kh.DIACHIKH,
                          sdtkh = kh.SDTKH
                      }).ToList<khachhang>();

            return kq;
        }

        //public List<thongtintdgia> laytuyen2()
        //{
        //    var bien = (from td in db.TUYENDUONGs
        //                from nd in db.NOIDIs
        //                from nden in db.NOIDENs
        //                where td.MANOIDEN == nden.MANOIDEN && td.MANOIDI == nd.MANOIDI
        //                select new thongtintdgia
        //                {
        //                    ma = td.MATUYEN,
        //                    ten = nd.TENNOIDI + " ==> " + nden.TENNOIDEN
        //                }).ToList<thongtintdgia>();

        //    return bien;
        //}

        public List<giochay> laygio(string matuyen, DateTime ngay)
        {
            var bien = (from g in db.LICHCHAYs
                        where g.MATUYEN == matuyen && g.NGAYCHAY == ngay
                        select new giochay
                        {
                            gio = g.GIO
                        }).ToList<giochay>();

            return bien;
        }

        public List<xechay> layxe(string matuyen, DateTime ngay, int gio)
        {
            var bien = (from g in db.LICHCHAYs
                        from x in db.XEs
                        where g.MAXE == x.MAXE && g.MATUYEN == matuyen && g.NGAYCHAY == ngay && g.GIO == gio && g.HUY == 0
                        select new xechay
                        {
                           maxe = g.MAXE,
                           bienso = x.BIENSO
                        }).ToList<xechay>();

            return bien;
        }

        public int laygiagannhat(string matuyen)
        {
            var bien = (from g in db.DONGIAs
                        from gt in db.GIATUYENs
                        from t in db.TUYENDUONGs
                        from noidi in db.NOIDIs
                        from noiden in db.NOIDENs
                        from abc in
                            (from abc in db.GIATUYENs
                             group abc by abc.MATUYEN into capnhatgannhat
                             select new
                             {
                                 matuyen = capnhatgannhat.Key,
                                 ngaycapnhatgannhat = capnhatgannhat.Max(a => a.NGAYCAPNHAT)
                             })
                        where g.MADONGIA == gt.MADONGIA && t.MATUYEN == gt.MATUYEN && t.MANOIDI == noidi.MANOIDI && t.MANOIDEN == noiden.MANOIDEN && gt.MATUYEN == abc.matuyen && gt.NGAYCAPNHAT == abc.ngaycapnhatgannhat
                        && t.MATUYEN == matuyen
                        select new 
                        {                           
                            g.GIATIEN
                            
                        }).FirstOrDefault().GIATIEN;
            return bien;
        }

        public List<ghedadat> layghedat(string matuyen, DateTime ngay, int gio, string maxe)
        {
            var bien = (from ve in db.VEs
                        where ve.MATUYEN == matuyen && ve.NGAYCHAY == ngay && ve.GIO == gio && ve.MAXE == maxe
                        select new ghedadat
                        {
                            ghe = int.Parse(ve.SOGHE.ToString())
                        }).ToList<ghedadat>();

            return bien;
        }

        public int sochongoi(string maxe)
        {
            return int.Parse(db.XEs.Where(x => x.MAXE == maxe).FirstOrDefault().SOCHONGOI.ToString());
        }

        public string mavelonnhat()
        {
            string ma = (from v in db.VEs
                         orderby v.MAVE descending
                         select v.MAVE).FirstOrDefault();
            return ma;
        }

        public void luuve(string mave, string manv, string maxe, string matuyen, DateTime ngaychay, string makh, int giochay, int soghe, DateTime ngaylapve, int giatien)
        {
            VE v = new VE();
            v.MAVE = mave;
            v.MANHANVIEN = manv;
            v.MAXE = maxe;
            v.MATUYEN = matuyen;
            v.NGAYCHAY = ngaychay;
            v.MAKHACHHANG = makh;
            v.GIO = giochay;
            v.SOGHE = soghe; 
            v.NGAYLAPVE = ngaylapve;
            v.GIAVE = giatien;

            db.VEs.InsertOnSubmit(v);
            db.SubmitChanges();
        }

        public string laymanv(string taikhoan)
        {
            return db.TAIKHOANs.Where(tk => tk.TAIKHOAN1 == taikhoan).FirstOrDefault().MANHANVIEN;
        }
    }

    public class CapTaiKhoan
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();
        public List<taikhoannv> nvcotk()
        {
            var bien = (from tk in db.TAIKHOANs
                        from nv in db.NHANVIENs
                        where tk.MANHANVIEN == nv.MANHANVIEN
                        select new taikhoannv
                        {
                           manv = tk.MANHANVIEN,
                           tennv = nv.TENNHANVIEN,
                           taikhoan = tk.TAIKHOAN1,
                           chucvu = tk.CHUCVU
                        }).ToList<taikhoannv>();

            return bien;
        }

        public List<nvchuacotk> nvchuacotk()
        {
            var bien = (from nv in db.NHANVIENs
                        where !(from tk in db.TAIKHOANs select tk.MANHANVIEN).Contains(nv.MANHANVIEN)
                        select new nvchuacotk
                        {
                            manv = nv.MANHANVIEN,
                            tennv = nv.TENNHANVIEN,                           
                        }).ToList<nvchuacotk>();

            return bien;
        }

        public string checktaikhoan(string tk)
        {
            string chuoitk = tk;
            int ck = 0;
            int k = 1;
            while (ck == 0)
            {
                var bien = db.TAIKHOANs.Where(t => t.TAIKHOAN1 == chuoitk).FirstOrDefault();
                if (bien == null)
                {
                    ck = 1;
                }
                else
                {
                    chuoitk = chuoitk + k;
                }
                k += 1;
            }
            return chuoitk;
        }

        public void themtk(string manv, string taikhoan, string matkhau, string chucvu)
        {
            TAIKHOAN tk = new TAIKHOAN();
            tk.MANHANVIEN = manv;
            tk.TAIKHOAN1 = taikhoan;
            tk.MATKHAU = matkhau;
            tk.CHUCVU = chucvu;

            db.TAIKHOANs.InsertOnSubmit(tk);
            db.SubmitChanges();
        }

        public void suachucvu(string taikhoan, string chucvu)
        {
            TAIKHOAN tk = db.TAIKHOANs.Where(g => g.TAIKHOAN1 == taikhoan).FirstOrDefault();            
            tk.CHUCVU = chucvu;

            db.SubmitChanges();
        }

        public void xoatk(string taikhoan)
        {
            TAIKHOAN tk = db.TAIKHOANs.Where(g => g.TAIKHOAN1 == taikhoan).FirstOrDefault();

            db.TAIKHOANs.DeleteOnSubmit(tk);
            db.SubmitChanges();
        }
    }

    public class TraCuuTuyenDuong
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();

        //public List<thongtintdgia> laytuyen()
        //{
        //    var bien = (from td in db.TUYENDUONGs
        //                from nd in db.NOIDIs
        //                from nden in db.NOIDENs
        //                where td.MANOIDEN == nden.MANOIDEN && td.MANOIDI == nd.MANOIDI
        //                select new thongtintdgia
        //                {
        //                    ma = td.MATUYEN,
        //                    ten = nd.TENNOIDI + " ==> " + nden.TENNOIDEN
        //                }).ToList<thongtintdgia>();

        //    return bien;
        //}

        public List<thongtintuyenduongtheongay> laytuyenngay(string matuyen,  DateTime ngaydi)
        {
            var bien = (from lc in db.LICHCHAYs
                        from x in db.XEs
                        where lc.MAXE == x.MAXE && lc.MATUYEN == matuyen && lc.NGAYCHAY == ngaydi
                        select new thongtintuyenduongtheongay
                        {
                            giodi = lc.GIO.ToString(),
                            thoigianchay = lc.THOIGIANCHAY.ToString(),
                            maxe = lc.MAXE,
                            socho = x.SOCHONGOI.ToString(),
                            bienso = x.BIENSO
                        }).ToList<thongtintuyenduongtheongay>();

            return bien;
        }

        public List<thongtintuyenduongchitiet> laytuyenchitiet(string matuyen, string maxe, DateTime ngaydi, int giodi)
        {
            var bien = (from lc in db.LICHCHAYs
                        from td in db.TUYENDUONGs
                        from noidi in db.NOIDIs
                        from noiden in db.NOIDENs                       
                        from diemdung in db.DIEMDUNGCHANs
                        from giatuyen in db.GIATUYENs
                        from abc in
                            (from abc in db.GIATUYENs
                             group abc by abc.MATUYEN into capnhatgannhat
                             select new
                             {
                                 matuyen = capnhatgannhat.Key,
                                 ngaycapnhatgannhat = capnhatgannhat.Max(a => a.NGAYCAPNHAT)
                             })
                        from dongia in db.DONGIAs
                        from x in db.XEs
                        from nvpt in db.NHANVIENPHUTRACHXEs
                        where lc.MATUYEN == td.MATUYEN && lc.MAXE == x.MAXE &&td.MANOIDI == noidi.MANOIDI && td.MANOIDEN == noiden.MANOIDEN && td.MADIEMDC == diemdung.MADIEMDC 
                        && td.MATUYEN == giatuyen.MATUYEN && giatuyen.MADONGIA == dongia.MADONGIA && giatuyen.MATUYEN == abc.matuyen && giatuyen.NGAYCAPNHAT == abc.ngaycapnhatgannhat && x.MAXE == nvpt.MAXE &&lc.MATUYEN == matuyen && lc.MAXE == maxe && lc.NGAYCHAY == ngaydi && lc.GIO == giodi && nvpt.CHUCVUNVPT == "Tài xế"
                        select new thongtintuyenduongchitiet
                        {
                            tuyenduong = noidi.TENNOIDI + " ==> " + noiden.TENNOIDEN,
                            ngaydi = DateTime.Parse(lc.NGAYCHAY.ToString()).ToString("dd/MM/yyyy"),
                            giodi = lc.GIO.ToString(),
                            thoigianchay = lc.THOIGIANCHAY.ToString(),
                            tenxe = x.TENXE,
                            bienso = x.BIENSO,
                            sochongoi = x.SOCHONGOI.ToString(),
                            tentaixe = nvpt.TENNVPT,
                            diemdungchan = diemdung.TENDIEMDC,
                            giave = dongia.GIATIEN.ToString()
                        }).ToList<thongtintuyenduongchitiet>();

            return bien;
        }

        public List<ghedadat> layghedat(string matuyen, string maxe, DateTime ngay, int gio )
        {
            var bien = (from ve in db.VEs
                        where ve.MATUYEN == matuyen && ve.NGAYCHAY == ngay && ve.GIO == gio && ve.MAXE == maxe
                        select new ghedadat
                        {
                            ghe = int.Parse(ve.SOGHE.ToString())
                        }).ToList<ghedadat>();

            return bien;
        }
    }

    public class TraCuuThongTinDatVe
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();
        public List<thongtindatve> laythongtinve(string mave)
        {
            var bien = (from ve in db.VEs
                        from nv in db.NHANVIENs
                        from kh in db.KHACHHANGs
                        from lc in db.LICHCHAYs
                        from x in db.XEs
                        from td in db.TUYENDUONGs
                        from noiden in db.NOIDENs
                        from noidi in db.NOIDIs
                        from diemdung in db.DIEMDUNGCHANs
                       
                        
                        where ve.MAKHACHHANG == kh.MAKHACHHANG && ve.MANHANVIEN == nv.MANHANVIEN && ve.GIO == lc.GIO && ve.NGAYCHAY == lc.NGAYCHAY && ve.MAXE == lc.MAXE && ve.MATUYEN == lc.MATUYEN
                        && lc.MATUYEN == td.MATUYEN && lc.MAXE == x.MAXE && td.MADIEMDC == diemdung.MADIEMDC && td.MANOIDEN == noiden.MANOIDEN && td.MANOIDI == noidi.MANOIDI && ve.MAVE == mave 
                        
                        select new thongtindatve
                        {
                           tenkh = kh.TENKHACHHANG,
                           diachi = kh.DIACHIKH,
                           sodt = kh.SDTKH,
                           tuyenduong = noidi.TENNOIDI +" ==> "+ noiden.TENNOIDEN,
                           tenxe = x.TENXE,
                           bienso = x.BIENSO,
                           sochongoi = x.SOCHONGOI.ToString(),
                           ghedat = ve.SOGHE.ToString(),
                           ngaydi = DateTime.Parse(lc.NGAYCHAY.ToString()).ToString("dd/MM/yyyy"),
                           giodi = lc.GIO.ToString(),
                           thoigianxechay = lc.THOIGIANCHAY.ToString(),
                           diemdung = diemdung.TENDIEMDC,
                           giave = ve.GIAVE.ToString(),
                           ngaylap = DateTime.Parse(ve.NGAYLAPVE.ToString()).ToString("dd/MM/yyyy"),
                           nhanvien = nv.TENNHANVIEN,
                        }).ToList<thongtindatve>();

            return bien;
        }

    }

    public class rptthongtintaikhoan
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();
        public List<rpttaikhoan> laythongtintaikhoan(string taikhoan)
        {
            var bien = (from tk in db.TAIKHOANs
                        from nv in db.NHANVIENs
                        where tk.TAIKHOAN1 == taikhoan && tk.MANHANVIEN == nv.MANHANVIEN
                        select new rpttaikhoan
                        {
                          manv = tk.MANHANVIEN,
                          tennv = nv.TENNHANVIEN,
                          taikhoan = tk.TAIKHOAN1,
                          matkhau = tk.MATKHAU,
                          chucvu = tk.CHUCVU
                        }).ToList<rpttaikhoan>();

            return bien;
        }
    }

    public class rptthongtinve
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();
        public List<rptbve> laythongtinve(string mave)
        {
            var bien = (from ve in db.VEs
                        from nv in db.NHANVIENs
                        from kh in db.KHACHHANGs
                        from x in db.XEs
                        from td in db.TUYENDUONGs
                        from nd in db.NOIDIs
                        from nden in db.NOIDENs
                        where ve.MAKHACHHANG == kh.MAKHACHHANG && ve.MANHANVIEN == nv.MANHANVIEN && ve.MAXE == x.MAXE && ve.MAVE == mave &&td.MANOIDEN == nden.MANOIDEN && td.MANOIDI == nd.MANOIDI && ve.MATUYEN ==td.MATUYEN
                        select new rptbve
                        {
                            mave = ve.MAVE,
                            tenkh = kh.TENKHACHHANG,
                            diachi = kh.DIACHIKH,
                            sdt = kh.SDTKH,
                            tuyenduong = nd.TENNOIDI + " ==> " + nden.TENNOIDEN,
                            giokh = ve.GIO.ToString() +"h" ,
                            ngaykh = DateTime.Parse(ve.NGAYCHAY.ToString()).ToString("dd/MM/yyyy"),
                            soghe = ve.SOGHE.ToString(),
                            xe = x.BIENSO,
                            giave = String.Format("{0:0,0 vnđ}",ve.GIAVE),
                            nhanvien = nv.TENNHANVIEN
                        }).ToList<rptbve>();

            return bien;
        }
    }

    public class Thongke
    {
        QuanLyHangXeKhachDataContext db = new QuanLyHangXeKhachDataContext();
        public List<laybiensoxe> laybienso()
        {
            var bien = (from x in db.XEs
                        select new laybiensoxe
                        {
                            maxe = x.MAXE,
                           bienso = x.BIENSO
                        }).ToList<laybiensoxe>();

            return bien;
        }

        public List<laytuyenduong> laytuyenduongtk()
        {
            var bien = (from tuyen in db.TUYENDUONGs
                        from noidi in db.NOIDIs
                        from noiden in db.NOIDENs
                        where tuyen.MANOIDI == noidi.MANOIDI && tuyen.MANOIDEN == noiden.MANOIDEN
                        select new laytuyenduong
                        {
                            matd = tuyen.MATUYEN,
                            tuyenduong = noidi.TENNOIDI +" ==> "+ noiden.TENNOIDEN
                        }).ToList<laytuyenduong>();

            return bien;
        }

        public int demtongkhach(DateTime ngaybd, DateTime ngaykt)
        { 
            return (from ve in db.VEs
                       where  ve.NGAYLAPVE >= ngaybd && ve.NGAYLAPVE < ngaykt
                       select new 
                       {
                            ve.MAVE
                       }).Count();          
           
        }

       

        public List<thongkeluongkhach> tklktheoxe(DateTime ngaybd, DateTime ngaykt)
        {
            var bien = (from a in 
                        (from x in db.XEs
                        from ve in db.VEs
                        where x.MAXE == ve.MAXE && ve.NGAYLAPVE >= ngaybd && ve.NGAYLAPVE < ngaykt                        
                        select new thongkeluongkhach
                        {
                            ten = x.BIENSO,
                            soluong = ve.MAVE
                        })
                        group a by a.ten into abc
                        select new thongkeluongkhach
                        {
                            ten = abc.Key,
                            soluong = abc.Count().ToString()
                        }).ToList<thongkeluongkhach>();

            return bien;
        }

        public List<thongkeluongkhach> tklktheoxe(DateTime ngaybd, DateTime ngaykt, string maxe)
        {
            var bien = (from a in
                            (from x in db.XEs
                             from ve in db.VEs
                             where x.MAXE == ve.MAXE && ve.NGAYLAPVE >= ngaybd && ve.NGAYLAPVE < ngaykt && x.MAXE == maxe
                             select new thongkeluongkhach 
                             {
                                 ten = x.BIENSO,
                                 soluong = ve.MAVE
                             })
                        group a by a.ten into abc
                        select new thongkeluongkhach
                        {
                            ten = abc.Key,
                            soluong = abc.Count().ToString()
                        }).ToList<thongkeluongkhach>();

            return bien;
        }

        public List<thongkeluongkhach> tklktheotuyen(DateTime ngaybd, DateTime ngaykt)
        {
            var bien = (from a in
                            (from td in db.TUYENDUONGs
                             from ve in db.VEs
                             from noiden in db.NOIDENs
                             from noidi in db.NOIDIs
                             where td.MATUYEN == ve.MATUYEN && ve.NGAYLAPVE >= ngaybd && ve.NGAYLAPVE < ngaykt && td.MANOIDI == noidi.MANOIDI && td.MANOIDEN == noiden.MANOIDEN
                             select new thongkeluongkhach
                             {
                                 ten = noidi.TENNOIDI +" ==> "+ noiden.TENNOIDEN,
                                 soluong = ve.MAVE
                             })
                        group a by a.ten into abc
                        select new thongkeluongkhach
                        {
                            ten = abc.Key,
                            soluong = abc.Count().ToString()
                        }).ToList<thongkeluongkhach>();

            return bien;
        }

        public List<thongkeluongkhach> tklktheotuyen(DateTime ngaybd, DateTime ngaykt, string matuyen)
        {
            var bien = (from a in
                            (from td in db.TUYENDUONGs
                             from ve in db.VEs
                             from noiden in db.NOIDENs
                             from noidi in db.NOIDIs
                             where td.MATUYEN == ve.MATUYEN && ve.NGAYLAPVE >= ngaybd && ve.NGAYLAPVE < ngaykt && td.MANOIDI == noidi.MANOIDI && td.MANOIDEN == noiden.MANOIDEN && td.MATUYEN == matuyen
                             select new thongkeluongkhach
                             {
                                 ten = noidi.TENNOIDI + " ==> " + noiden.TENNOIDEN,
                                 soluong = ve.MAVE
                             })
                        group a by a.ten into abc
                        select new thongkeluongkhach
                        {
                            ten = abc.Key,
                            soluong = abc.Count().ToString()
                        }).ToList<thongkeluongkhach>();

            return bien;
        }

        public List<thongkedoanhthu> tkdttheoxe(DateTime ngaybd, DateTime ngaykt)
        {
            var bien = (from a in
                            (from x in db.XEs
                             from ve in db.VEs
                             where x.MAXE == ve.MAXE && ve.NGAYLAPVE >= ngaybd && ve.NGAYLAPVE < ngaykt
                             select new thongkedoanhthu
                             {
                                 ten = x.BIENSO,
                                 doanhthu = Convert.ToInt32(ve.GIAVE)
                             })
                        group a by a.ten into abc
                        select new thongkedoanhthu
                        {
                            ten = abc.Key,
                            doanhthu = abc.Sum(k => k.doanhthu)
                        }).ToList<thongkedoanhthu>();

            return bien;
        }

        public List<thongkedoanhthu> tkdttheoxe(DateTime ngaybd, DateTime ngaykt, string maxe )
        {
            var bien = (from a in
                            (from x in db.XEs
                             from ve in db.VEs
                             where x.MAXE == ve.MAXE && ve.NGAYLAPVE >= ngaybd && ve.NGAYLAPVE < ngaykt && x.MAXE == maxe
                             select new thongkedoanhthu
                             {
                                 ten = x.BIENSO,
                                 doanhthu = Convert.ToInt32(ve.GIAVE)
                             })
                        group a by a.ten into abc
                        select new thongkedoanhthu
                        {
                            ten = abc.Key,
                            doanhthu = abc.Sum(k => k.doanhthu)
                        }).ToList<thongkedoanhthu>();

            return bien;
        }

        public List<thongkedoanhthu> tkdttheotuyen(DateTime ngaybd, DateTime ngaykt)
        {
            var bien = (from a in
                            (from td in db.TUYENDUONGs
                             from ve in db.VEs
                             from noiden in db.NOIDENs
                             from noidi in db.NOIDIs
                             where td.MATUYEN == ve.MATUYEN && ve.NGAYLAPVE >= ngaybd && ve.NGAYLAPVE < ngaykt && td.MANOIDI == noidi.MANOIDI && td.MANOIDEN == noiden.MANOIDEN
                             select new thongkedoanhthu
                             {
                                 ten = noidi.TENNOIDI + " ==> " + noiden.TENNOIDEN,
                                 doanhthu = Convert.ToInt32(ve.GIAVE)
                             })
                        group a by a.ten into abc
                        select new thongkedoanhthu
                        {
                            ten = abc.Key,
                            doanhthu = abc.Sum(k => k.doanhthu)
                        }).ToList<thongkedoanhthu>();

            return bien;
        }

        public List<thongkedoanhthu> tkdttheotuyen(DateTime ngaybd, DateTime ngaykt, string matuyen)
        {
            var bien = (from a in
                            (from td in db.TUYENDUONGs
                             from ve in db.VEs
                             from noiden in db.NOIDENs
                             from noidi in db.NOIDIs
                             where td.MATUYEN == ve.MATUYEN && ve.NGAYLAPVE >= ngaybd && ve.NGAYLAPVE < ngaykt && td.MANOIDI == noidi.MANOIDI && td.MANOIDEN == noiden.MANOIDEN && td.MATUYEN == matuyen
                             select new thongkedoanhthu
                             {
                                 ten = noidi.TENNOIDI + " ==> " + noiden.TENNOIDEN,
                                 doanhthu = Convert.ToInt32(ve.GIAVE)
                             })
                        group a by a.ten into abc
                        select new thongkedoanhthu
                        {
                            ten = abc.Key,
                            doanhthu = abc.Sum(k => k.doanhthu)
                        }).ToList<thongkedoanhthu>();

            return bien;
        }

        public int demtongdoanhthu(DateTime ngaybd, DateTime ngaykt)
        {
            return Convert.ToInt32((from ve in db.VEs
                    where ve.NGAYLAPVE >= ngaybd && ve.NGAYLAPVE < ngaykt
                    select new 
                    {
                        ve.GIAVE
                    }).Sum(k => k.GIAVE));

        }
        
    }
}
